from openpyxl import load_workbook
import os

filename = os.getcwd() + os.sep + 'test.xlsx'
workbook = load_workbook(filename)
print(workbook.sheetnames)

sheet = workbook.active

#写入单元格
cell = sheet['A1']
cell.value = 'hello world'
workbook.save(filename)

print(sheet['A1'].value)

data1 = ['xiaoming','22','M']
data2 = ['xiaohong','20','F']
sheet.append(data1)
sheet.append(data2)
workbook.save(filename)

#将公式写入单元格并保存
sheet['A11'] = '=AVERAGE(K1:K10)'
workbook.save(filename)

#插入行或列
#insert_rows(idx, amount)中 idx指定插入的位置，amount指定插入的行数
sheet.insert_rows(2,2)
workbook.save(filename)
#insert_cols(idx, amount)中idx指定插入的位置，amount指定插入的列数
sheet.insert_cols(2,2)
workbook.save(filename)

#删除行或列
sheet.delete_rows(2,2)
sheet.delete_cols(2,2)
workbook.save(filename)

#移动范围数据
#sheet.move_range('xx:xx', rows=n, cols=n) 其中数字为正为向下或向右，数字为负为向上或向右
sheet.move_range('D1:F2',rows=4, cols=-3)
workbook.save(filename)

